<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Encoder_model extends CI_Model {
	
	public function __construct()
    {
        parent::__construct();
    }
	
	public function check_username($uname){
		$this->load->database();
		$sql = "SELECT username FROM encoder_account WHERE username = ?";
		$query = $this->db->query($sql, $uname);
	
		if ($query->num_rows() > 0)
			return true;
		return false;
	}
	
	public function check_login($account){
		$this->load->database();
		$sql = "SELECT username,password FROM encoder_account WHERE username = ? AND password = ?"; 
		$query = $this->db->query($sql, array($account['username'], $account['password']));
		
		if($query->num_rows() == 0)
			$query = NULL;
		return $query;
	}
	
	public function studAcadInfo($stdno){
		$this->load->database();
		
		/* Getting values from student_account table. */
		$sql = "SELECT student_no,reg_adviser,sp_adviser,curriculum FROM student_account WHERE student_no = ?"; 
		$query = $this->db->query($sql, $stdno);
		
		foreach($query->result() as $row)
			$d['stdno'] = $row->student_no;
			$d['reg_adviser'] = $row->reg_adviser;
			$d['sp_adviser'] = $row->sp_adviser;
			$d['curriculum'] = $row->curriculum;
			
		/* Get names of SP Adviser and Reg Adviser */
		$this->db->select('name');
		$this->db->where('emp_id', $d['reg_adviser']);
		$query = $this->db->get('instructor_account');
		
		foreach($query->result() as $row){
			$d['reg_adviser'] = $row->name;
		}
		
		$this->db->select('name');
		$this->db->where('emp_id', $d['sp_adviser']);
		$query = $this->db->get('instructor_account');
		
		foreach($query->result() as $row){
			$d['sp_adviser'] = $row->name;
		}
		
		/* Getting the list of approved GEs. */
		$sql = "SELECT course_name FROM approved_ge WHERE student_no_fk = ?";
		$query = $this->db->query($sql, $stdno);
		$approved_ge = array();
		foreach($query->result() as $row){
			$approved_ge[] = $row->course_name;
		}
		$d['approved_ge'] = $approved_ge;
		
		/* Getting the list of approved Electives. */
		$sql = "SELECT course_name FROM approved_electives WHERE student_no_fk = ?";
		$query = $this->db->query($sql, $stdno);
		$approved_electives = array();
		foreach($query->result() as $row){
			$approved_electives[] = $row->course_name;
		}
		$d['approved_electives'] = $approved_electives;
		
		/* Getting the list of subjects taken. */
		$sql = "SELECT course_no_fk, units, grade, sem_taken, year, taken_type FROM subjects_taken WHERE student_no_fk = ?";
		$query = $this->db->query($sql, $stdno);
		$subjects_taken = array();
	
		foreach($query->result() as $row){
			$subjects_taken[] = array(
							'course_no_fk' => $row->course_no_fk,
							'units' => $row->units,
							'grade' => $row->grade,
							'sem' => $row->sem_taken,
							'year' => $row->year,
							'type' => $row->taken_type,
				);
		}
		$d['subjects_taken'] = $subjects_taken;
		
		/* Cases Involved */
		$sql = "SELECT case_description,date1 FROM cases WHERE student_no_fk = ?";
		$query = $this->db->query($sql, $stdno);
		$cases = array();
		foreach($query->result() as $row){
			$cases[] = array(
					'case_description' => $row->case_description,
					'date' => $row->date1,
				);
		}
		$d['cases'] = $cases;
		
		/* Academic History */
		$sql = "SELECT semester,year,gwa,standing FROM academic_history WHERE student_no_fk = ?";
		$query = $this->db->query($sql, $stdno);
		$academic_history = array();
	
		foreach($query->result() as $row){
			$academic_history[] = array(
							'sem' => $row->semester,
							'year' => $row->year,
							'gwa' => $row->gwa,
							'standing' => $row->standing,
				);
		}
		$d['academic_history'] = $academic_history;
		return $d;
	}
	
	public function getInstructors(){
		$this->load->database();
		
		$this->db->select('emp_id,name');
		$query = $this->db->get('instructor_account');
		
		$data = array();
		foreach($query->result() as $row)
			$data[$row->emp_id] = $row->name;
		
		return $data;
	}
	
	public function getGEah(){
		$this->load->database();
		
		$geah = "ge (ah)";
		$this->db->select('course_number');
		$this->db->where('type', $geah);
		$query = $this->db->get('course');
		$ah = array();
		foreach($query->result() as $row){
			$ah[$row->course_number] = $row->course_number;
		}
		return $ah;
	}
	
	public function getGEssp(){
		$this->load->database();
		
		$gessp = "ge (ssp)";
		$this->db->select('course_number');
		$this->db->where('type', $gessp);
		$query = $this->db->get('course');
		$ssp = array();
		foreach($query->result() as $row){
			$ssp[$row->course_number] = $row->course_number;
		}
		return $ssp;
	}
	
	public function getGEmst(){
		$this->load->database();
		
		$gemst = "ge (mst)";
		$this->db->select('course_number');
		$this->db->where('type', $gemst);
		$query = $this->db->get('course');
		$mst = array();
		foreach($query->result() as $row){
			$mst[$row->course_number] = $row->course_number;
		}
		return $mst;
	}
	
	public function getElectiveAM(){	
		$this->load->database();
		$e_am = "elective (am)";
		$this->db->select('course_number');
		$this->db->where('type', $e_am);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getElectiveCS(){	
		$this->load->database();
		$e_cs = "elective (cs)";
		$this->db->select('course_number');
		$this->db->where('type', $e_cs);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getElectiveDC(){	
		$this->load->database();
		$e_dc = "elective (dc)";
		$this->db->select('course_number');
		$this->db->where('type', $e_dc);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getElectiveEM(){	
		$this->load->database();
		$e_em = "elective (em)";
		$this->db->select('course_number');
		$this->db->where('type', $e_em);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getElectiveHUM(){	
		$this->load->database();
		$e_hum = "elective (hum)";
		$this->db->select('course_number');
		$this->db->where('type', $e_hum);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getElectiveFRM(){	
		$this->load->database();
		$e_frm = "elective (frm)";
		$this->db->select('course_number');
		$this->db->where('type', $e_frm);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getElectiveP(){	
		$this->load->database();
		$e_p = "elective (p)";
		$this->db->select('course_number');
		$this->db->where('type', $e_p);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getElectiveSS(){	
		$this->load->database();
		$e_ss = "elective (ss)";
		$this->db->select('course_number');
		$this->db->where('type', $e_ss);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getElectiveSTAT(){	
		$this->load->database();
		$e_stat = "elective (stat)";
		$this->db->select('course_number');
		$this->db->where('type', $e_stat);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getElectiveIE(){	
		$this->load->database();
		$e_ie = "elective (ie)";
		$this->db->select('course_number');
		$this->db->where('type', $e_ie);
		$query = $this->db->get('course');
		$elective = array();
		foreach($query->result() as $row)
			$elective[$row->course_number] = $row->course_number;
		return $elective;
	}
	
	public function getCurr($stdno){
		$this->load->database();
		
		$this->db->select('curriculum');
		$this->db->where('student_no', $stdno);
		$query = $this->db->get('student_account');
		
		foreach($query->result() as $row)
			$c = $row->curriculum;
		return $c;
	}
	
	public function check_curriculum_null($std_no){
		$this->load->database();
		
		$sql = "SELECT curriculum FROM student_account WHERE student_no = ?";
		$query = $this->db->query($sql, $std_no);
		
		foreach($query->result() as $row)
			$curriculum = $row->curriculum;
		
		if($curriculum == NULL)
			return true;
		return false;
	}
	
	public function check_reg_adviser_null($std_no){
		$this->load->database();
		
		$sql = "SELECT reg_adviser FROM student_account WHERE student_no = ?";
		$query = $this->db->query($sql, $std_no);
		
		foreach($query->result() as $row)
			$reg_adviser = $row->reg_adviser;
		
		if($reg_adviser == NULL)
			return true;
		return false;
	}
	
	public function check_sp_adviser_null($std_no){
		$this->load->database();
		
		$sql = "SELECT sp_adviser FROM student_account WHERE student_no = ?";
		$query = $this->db->query($sql, $std_no);
		
		foreach($query->result() as $row)
			$sp_adviser = $row->sp_adviser;
		
		if($sp_adviser == NULL)
			return true;
		return false;
	}
	
	public function addCurriculumAndAdviser($data){
		$this->load->database();

		$stdno = $data['stdno'];
		$data = array(
			'curriculum' => $data['curriculum'],
			'reg_adviser' => $data['reg_adviser'],
			'sp_adviser' => $data['sp_adviser'],
		);
		
		$this->db->where('student_no', $stdno);
		$this->db->update('student_account', $data); 
	}
	
	public function getCurrRegAdviser($stdno){
		$this->load->database();
	
		/*Retrieve current Reg Adviser from student_account table*/
		$sql = "SELECT reg_adviser FROM student_account WHERE student_no = ?";
		$query = $this->db->query($sql, $stdno);
		
		foreach($query->result() as $row)
			$emp_id = $row->reg_adviser;
		return $emp_id;
	}
	
	public function getCurrSPAdviser($stdno){
		$this->load->database();
	
		/*Retrieve current SP Adviser from student_account table*/
		$sql = "SELECT sp_adviser FROM student_account WHERE student_no = ?";
		$query = $this->db->query($sql, $stdno);
		
		foreach($query->result() as $row)
			$emp_id = $row->sp_adviser;
		return $emp_id;
	}
	
	public function getCurrGEs($stdno){
		$this->load->database();
		/* Getting the list of approved GEs. */
		$this->db->select('course_name');
		$this->db->where('student_no_fk', $stdno);
		$query = $this->db->get('approved_ge');

		$approved_ge = array();
		foreach($query->result() as $row){
			$approved_ge[] = $row->course_name;
		}
		return $approved_ge;
	}
	
	public function getCurrElectives($stdno){
		$this->load->database();
		/* Getting the list of approved GEs. */		
		$this->db->select('course_name');
		$this->db->where('student_no_fk', $stdno);
		$query = $this->db->get('approved_electives');
		
		$approved_electives = array();
		foreach($query->result() as $row)
			$approved_electives[] = $row->course_name;
		return $approved_electives;
	}
	
	public function changeCurriculumAndAdviser($data){
		$this->load->database();
		
		$stdno = $data['stdno'];
		
		/*Retrieve current SP Adviser from student_account table*/
		$sql = "SELECT sp_adviser FROM student_account WHERE student_no = ?";
		$query = $this->db->query($sql, $stdno);
		
		foreach($query->result() as $row)
			$emp_id = $row->sp_adviser;
		
		/* Add the old sp adviser to prev_sp_adviser table */
		$data2 = array(
			'emp_no' => $emp_id,
			'stud_no_fk' => $stdno,
		);
		
		$this->db->insert('prev_sp_adviser', $data2); 
		
		/* Insert student_no to prev_advisees table */
		$data3 = array(
			'emp_no_fk' => $emp_id,
			'student_id' => $stdno,
		);
		$this->db->insert('prev_advisees',$data3);
		
		/* Change curr sp adviser and other changed values in student_account table */
		$data = array(
			'curriculum' => $data['curriculum'],
			'reg_adviser' => $data['reg_adviser'],
			'sp_adviser' => $data['sp_adviser'],
		);
		$this->db->where('student_no', $stdno);
		$this->db->update('student_account', $data); 
	}
	
	public function checkIfInsertOrUpdate($data){
		$this->load->database();
		$stdno = $data['stdno'];
		
		$sql = "SELECT student_no_fk FROM approved_ge WHERE student_no_fk = ?";
		$query = $this->db->query($sql, $stdno);
		$r = 0;
		foreach($query->result() as $row)
			$r = $row->student_no_fk;
		
		if($r == NULL)
			return true;	//add
		return false;		//edit
	}
	
	public function checkIfInsertOrUpdateElective($data){
		$this->load->database();
		$stdno = $data['stdno'];
		
		$sql = "SELECT student_no_fk FROM approved_electives WHERE student_no_fk = ?";
		$query = $this->db->query($sql, $stdno);
		$r = 0;
		foreach($query->result() as $row)
			$r = $row->student_no_fk;
		
		if($r == NULL)
			return true;	//add
		return false;		//edit
	}
	
	public function addGE($data){
		$this->load->database();
		
		$stdno = $data['stdno'];
		$ah = $data['ah'];
		$ssp = $data['ssp'];
		$mst = $data['mst'];
		
		/* Insert AH */
		foreach($ah as $key  => $value){
			$data = array(
				'student_no_fk' => $stdno,
				'course_name' => $value,
			);
			$this->db->insert('approved_ge', $data); 
		}
		/* Insert SSP */
		foreach($ssp as $key  => $value){
			$data = array(
				'student_no_fk' => $stdno,
				'course_name' => $value,
			);
			$this->db->insert('approved_ge', $data); 
		}
		/* Insert MST */
		foreach($mst as $key  => $value){
			$data = array(
				'student_no_fk' => $stdno,
				'course_name' => $value,
			);
			$this->db->insert('approved_ge', $data); 
		}
	}
	
	public function addElectives($data){
		$this->load->database();
		
		$stdno = $data['stdno'];
		$electives = $data['electives'];
		
		/* Insert Electives */
		foreach($electives as $key  => $value){
			$data = array(
				'student_no_fk' => $stdno,
				'course_name' => $value,
			);
			$this->db->insert('approved_electives', $data); 
		}
	}
	
	public function deleteGE($data){
		$this->load->database();
		$stdno = $data['stdno'];
		$this->db->where('student_no_fk', $stdno);
		$this->db->delete('approved_ge'); 
	}
	
	public function deleteElectives($data){
		$this->load->database();
		$stdno = $data['stdno'];
		$this->db->where('student_no_fk', $stdno);
		$this->db->delete('approved_electives'); 
	}

	public function add_account($account)
	{
		$this->load->database();
		$sql = "INSERT INTO encoder_account(username,password,name,role,approved,activated) VALUES (?,?,?,?,?,?)";
		$query = $this->db->query($sql, array($account['username'], $account['password'],
			$account['name'], $account['role'], $account['approved'], $account['activated']));	
		return $query;
	}

	public function getUnapproved() {
		$this->load->database();
		$sql = "SELECT username FROM encoder_account WHERE approved = 0";
		$query = $this->db->query($sql);
		return $query->result_array();		
	}

	public function delete_account($username) {
		$this->load->database();
		$this->db->where('username', $username);
		$this->db->delete('encoder_account');
	}

	public function checkif_courseexist($c){
		$this->load->database();
	
		$this->db->select('course_number');
		$this->db->where('course_number', $c);
		$query = $this->db->get('course');
		
		$course = "";
		foreach($query->result() as $row)
			$course = $row->course_number;
		
		if($course == NULL)
			return false;
		return true;
	}
	
	public function yearsemexist($stdno,$year,$sem){
		$this->load->database();
		
		$this->db->select('gwa');
		$this->db->where('student_no_fk', $stdno);
		$this->db->where('year', $year);
		$this->db->where('semester', $sem); 
		
		$query = $this->db->get('academic_history');
		$gwa = 0;
		foreach($query->result() as $row)
			$gwa = $row->gwa;
		
		if($gwa == NULL)
			return true;
		return false;
	}
	
	public function addSubjects($d){
		$this->load->database();
		
		/* Insert data in academic_history table */
		$data = array(
			'student_no_fk' => $d['data8']['stdno'],
			'semester' => $d['data8']['sem'],
			'year' => $d['data8']['year'],
			'gwa' => $d['data8']['gwa'],
			'standing' => $d['data8']['standing'],
		);
		
		$this->db->insert('academic_history', $data); 
		
		/* Get units and type of data1*/
		$this->db->select('units,type');
		$this->db->where('course_number', $d['data1']['course']);
		$query = $this->db->get('course');
		
		foreach($query->result() as $row){
			$units = $row->units;
			$type = $row->type;
		}
		
		$data = array(
			'student_no_fk' => $d['data8']['stdno'],
			'course_no_fk' => $d['data1']['course'],
			'units' => $units,
			'grade' => $d['data1']['grade'],
			'sem_taken' => $d['data8']['sem'],
			'year' => $d['data8']['year'],
			'taken_type' => $type,
		);
		$this->db->insert('subjects_taken', $data); 
		
		/* Check if other fields are not null and add data if not null. */
		if($d['data2']['course'] != NULL){
			/* Get units and type of data2*/
			$this->db->select('units,type');
			$this->db->where('course_number', $d['data2']['course']);
			$query = $this->db->get('course');
		
			foreach($query->result() as $row){
				$units = $row->units;
				$type = $row->type;
			}
		
			$data2 = array(
				'student_no_fk' => $d['data8']['stdno'],
				'course_no_fk' => $d['data2']['course'],
				'units' => $units,
				'grade' => $d['data2']['grade'],
				'sem_taken' => $d['data8']['sem'],
				'year' => $d['data8']['year'],
				'taken_type' => $type,
			);
			$this->db->insert('subjects_taken', $data2); 
		}
		
		if($d['data3']['course'] != NULL){
			/* Get units and type of data3*/
			$this->db->select('units,type');
			$this->db->where('course_number', $d['data3']['course']);
			$query = $this->db->get('course');
		
			foreach($query->result() as $row){
				$units = $row->units;
				$type = $row->type;
			}
		
			$data3 = array(
				'student_no_fk' => $d['data8']['stdno'],
				'course_no_fk' => $d['data3']['course'],
				'units' => $units,
				'grade' => $d['data3']['grade'],
				'sem_taken' => $d['data8']['sem'],
				'year' => $d['data8']['year'],
				'taken_type' => $type,
			);
			$this->db->insert('subjects_taken', $data3); 
		}
		
		if($d['data4']['course'] != NULL){
			/* Get units and type of data4*/
			$this->db->select('units,type');
			$this->db->where('course_number', $d['data4']['course']);
			$query = $this->db->get('course');
		
			foreach($query->result() as $row){
				$units = $row->units;
				$type = $row->type;
			}
		
			$data4 = array(
				'student_no_fk' => $d['data8']['stdno'],
				'course_no_fk' => $d['data4']['course'],
				'units' => $units,
				'grade' => $d['data4']['grade'],
				'sem_taken' => $d['data8']['sem'],
				'year' => $d['data8']['year'],
				'taken_type' => $type,
			);
			$this->db->insert('subjects_taken', $data4); 
		}
		
		if($d['data5']['course'] != NULL){
			/* Get units and type of data5*/
			$this->db->select('units,type');
			$this->db->where('course_number', $d['data5']['course']);
			$query = $this->db->get('course');
		
			foreach($query->result() as $row){
				$units = $row->units;
				$type = $row->type;
			}
		
			$data5 = array(
				'student_no_fk' => $d['data8']['stdno'],
				'course_no_fk' => $d['data5']['course'],
				'units' => $units,
				'grade' => $d['data5']['grade'],
				'sem_taken' => $d['data8']['sem'],
				'year' => $d['data8']['year'],
				'taken_type' => $type,
			);
			$this->db->insert('subjects_taken', $data5); 
		}
		
		if($d['data6']['course'] != NULL){
			/* Get units and type of data6*/
			$this->db->select('units,type');
			$this->db->where('course_number', $d['data6']['course']);
			$query = $this->db->get('course');
		
			foreach($query->result() as $row){
				$units = $row->units;
				$type = $row->type;
			}
		
			$data6 = array(
				'student_no_fk' => $d['data8']['stdno'],
				'course_no_fk' => $d['data6']['course'],
				'units' => $units,
				'grade' => $d['data6']['grade'],
				'sem_taken' => $d['data8']['sem'],
				'year' => $d['data8']['year'],
				'taken_type' => $type,
			);
			$this->db->insert('subjects_taken', $data6); 
		}
		
		if($d['data7']['course'] != NULL){
			/* Get units and type of data7*/
			$this->db->select('units,type');
			$this->db->where('course_number', $d['data7']['course']);
			$query = $this->db->get('course');
		
			foreach($query->result() as $row){
				$units = $row->units;
				$type = $row->type;
			}
		
			$data7 = array(
				'student_no_fk' => $d['data8']['stdno'],
				'course_no_fk' => $d['data7']['course'],
				'units' => $units,
				'grade' => $d['data7']['grade'],
				'sem_taken' => $d['data8']['sem'],
				'year' => $d['data8']['year'],
				'taken_type' => $type,
			);
			$this->db->insert('subjects_taken', $data7); 
		}
	}
	
	public function getSubyearsem($result){
		$this->load->database();
	
		$this->db->select('course_no_fk,grade');
		$this->db->where('student_no_fk', $result['stdno']);
		$this->db->where('year', $result['year']);
		$this->db->where('sem_taken', $result['sem']); 
		
		$query = $this->db->get('subjects_taken');
		$data = array();
		
		foreach($query->result() as $row){
			$data[] = array(
					'course' => $row->course_no_fk,
					'grade' => $row->grade,
			);
		}
		return $data;
	}
	
	public function getGWAStanding($result){
		$this->load->database();
		$this->db->select('gwa,standing');
		$this->db->where('student_no_fk', $result['stdno']);
		$this->db->where('year', $result['year']);
		$this->db->where('semester', $result['sem']); 
		
		$query = $this->db->get('academic_history');
		
		foreach($query->result() as $row){
			$data['gwa'] = $row->gwa;
			$data['standing'] = $row->standing;
		}
		return $data;
	}
	
	public function deleteSubjects($data){
		$this->load->database();
		
		$this->db->where('student_no_fk', $data['stdno']);
		$this->db->where('year', $data['year']);
		$this->db->where('sem_taken', $data['sem']); 
		$this->db->delete('subjects_taken'); 
	
		$this->db->where('student_no_fk', $data['stdno']);
		$this->db->where('year', $data['year']);
		$this->db->where('semester', $data['sem']); 
		$this->db->delete('academic_history'); 
	}
	
	public function getCases($stdno){
		$this->load->database();
		
		$this->db->select('case_description,date1');
		$this->db->where('student_no_fk', $stdno);
		$query = $this->db->get('cases');
		
		$data = array();
		foreach($query->result() as $row)
			$data[$row->date1] = $row->case_description;
		
		return $data;
	}
	
	public function addCase($data){
		$this->load->database();
		$d = array(
			'student_no_fk' => $data['stdno'],
			'date1' => $data['date'],
			'case_description' => $data['case'],
		);	
		$this->db->insert('cases', $d); 
	}
	
	public function addToLog($message,$name){
		$this->load->database();
		$d = array(
			'name' => $name,
			'action' => $message,
		);	
		$this->db->insert('log', $d); 
	}

	public function activate($username) {
		$this->load->database();
		$this->db->where('username', $username);
		$this->db->update('encoder_account', array('activated' => 1));
	}

	public function deactivate($username) {
		$this->load->database();
		$this->db->where('username', $username);
		$this->db->update('encoder_account', array('activated' => 0));
	}

	public function getActivatedStatus() {
		$this->load->database();
		$this->db->select('username, activated');
		$this->db->where('approved', 1);
		$query = $this->db->get('encoder_account');

		return $query->result_array();
	}

	public function getAllUsernames() {
		$this->load->database();
		$this->db->select('username');
		$this->db->where('approved', 1);
		$query = $this->db->get('encoder_account');

		return $query->result_array();
	}
}
